CREATE SEQUENCE SEQ_ID_POBYTU
		MINVALUE 1 
		MAXVALUE 999999999999999999 
		START WITH 1 
		INCREMENT BY 1 
		NOCACHE
;

create or replace
TRIGGER TRG_POBYT_INSERT 
		BEFORE INSERT ON POBYT 
			FOR EACH ROW 
			BEGIN 
				IF :new.ID_POBYTU IS NULL THEN
					SELECT SEQ_ID_POBYTU.nextval INTO :new.ID_POBYTU FROM DUAL; 
				END IF; 
			END; 
	/
  COMMIT;
  
CREATE SEQUENCE SEQ_ID_TAXY
		MINVALUE 1 
		MAXVALUE 999999999999999999 
		START WITH 1 
		INCREMENT BY 1 
		NOCACHE
;

create or replace
TRIGGER TRG_TAXA_INSERT 
		BEFORE INSERT ON TAXA 
			FOR EACH ROW 
			BEGIN 
				IF :new.ID_TAXY IS NULL THEN
					SELECT SEQ_ID_TAXY.nextval INTO :new.ID_TAXY FROM DUAL; 
				END IF; 
			END; 
	/
  COMMIT;
  
CREATE SEQUENCE SEQ_ID_STRAVOVANI
		MINVALUE 1 
		MAXVALUE 999999999999999999 
		START WITH 1 
		INCREMENT BY 1 
		NOCACHE
;

create or replace
TRIGGER TRG_STRAVOVANI_INSERT 
		BEFORE INSERT ON STRAVOVANI 
			FOR EACH ROW 
			BEGIN 
				IF :new.ID_STRAVOVANI IS NULL THEN
					SELECT SEQ_ID_STRAVOVANI.nextval INTO :new.ID_STRAVOVANI FROM DUAL; 
				END IF; 
			END; 
	/
  COMMIT;  

  CREATE SEQUENCE SEQ_ID_SLUZBY
	    MINVALUE 1 
	    MAXVALUE 999999999999999999 
	    START WITH 1 
	    INCREMENT BY 1 
	    NOCACHE
;

create or replace
TRIGGER TRG_SLUZBA_INSERT 
	    BEFORE INSERT ON SLUZBA
	        FOR EACH ROW 
	        BEGIN 
	            IF :new.ID_SLUZBY IS NULL THEN
	                SELECT SEQ_ID_SLUZBY.nextval INTO :new.ID_SLUZBY FROM DUAL; 
	            END IF; 
	        END; 
	/
  COMMIT;

create or replace
procedure projekt_vloz_pobyt(  
  p_id_taxy IN pobyt.id_taxy%TYPE,
  p_prijezd IN pobyt.prijezd%TYPE,
  p_odjezd IN pobyt.odjezd%TYPE,
  p_id_pokoje IN pokoj.id_pokoje%TYPE,
  p_rodne_cislo IN host.rodne_cislo%TYPE,
  p_id_stravovani IN stravovani.id_stravovani%TYPE
)
AS
v_pocet_dni int;
v_cena_stravovani int;
v_celkova_suma int;
v_cena_pokoje int;
v_cena_taxy int;
v_id_pobytu int;
v_id_pobytu_col int;
date_colision EXCEPTION;
BEGIN  
dbms_output.put_line('1');

  INSERT INTO Pobyt(id_taxy, prijezd, odjezd, celkova_suma, zaplaceno) VALUES (p_id_taxy, p_prijezd, p_odjezd, 0, 0);  
  
dbms_output.put_line('2');
  v_id_pobytu := SEQ_ID_POBYTU.CURRVAL;
  
  INSERT INTO Objednavka_pokoje(id_pobytu, id_pokoje) VALUES(v_id_pobytu, p_id_pokoje);  
  
  INSERT INTO Objednavka_stravovani(id_pobytu, id_stravovani, mnozstvi_stravovani) VALUES(v_id_pobytu, p_id_stravovani, 1);
  INSERT INTO Seznam_hostu(id_pobytu, rodne_cislo) VALUES(v_id_pobytu, p_rodne_cislo);
  
  select odjezd-prijezd into v_pocet_dni
  from Pobyt where id_pobytu = v_id_pobytu;
  
  select t.cena_taxy into v_cena_taxy from Taxa t, Pobyt p
  where p.id_taxy=t.id_taxy and p.id_pobytu=v_id_pobytu;
  
  select p.cena_pokoje into v_cena_pokoje from Pokoj p, objednavka_pokoje o
  where p.id_pokoje=o.id_pokoje and o.id_pobytu=v_id_pobytu;
    
  select sum(s.cena_stravovani*o.mnozstvi_stravovani) into v_cena_stravovani
  from stravovani s, objednavka_stravovani o
  where o.id_stravovani = s.id_stravovani and o.id_pobytu = v_id_pobytu;
  
  v_celkova_suma := (v_cena_pokoje+v_cena_taxy+v_cena_stravovani)*v_pocet_dni;  
  update Pobyt
  set celkova_suma = v_celkova_suma
  where id_pobytu = v_id_pobytu;
  
--KONTROLA OBSAZENOSTI
  select p.id_pobytu INTO v_id_pobytu_col from objednavka_pokoje o, Pobyt p
    where p_prijezd < odjezd AND p_odjezd > prijezd and p.id_pobytu = o.id_pobytu and o.id_pokoje = p_id_pokoje;      
  dbms_output.put_line('Pokoj je volny');

  commit;
  
  exception
    when TOO_MANY_ROWS THEN
      dbms_output.put_line('Pokoj je jiz obsazen');
      raise TOO_MANY_ROWS;
      rollback;
    when others then
      dbms_output.put_line('Chyba pri vkladani pobytu');
      raise_application_error(-20101, 'Chyba pri vkladani pobytu');
      rollback;        
END;

create or replace
procedure projekt_add_host(
p_rodne_cislo IN Host.rodne_cislo%TYPE,
p_jmeno IN host.jmeno%TYPE,
p_prijmeni IN host.prijmeni%TYPE,
p_ulice IN host.ulice%TYPE,
p_mesto IN host.mesto%TYPE, 
p_psc IN host.psc%TYPE, 
p_telefon IN host.telefon%TYPE, 
p_email IN host.email%TYPE
)
AS
begin
    INSERT INTO Host (rodne_cislo, jmeno, prijmeni, ulice, mesto, psc, telefon, email) 
    VALUES (p_rodne_cislo, p_jmeno, p_prijmeni, p_ulice, p_mesto, p_psc, p_telefon, p_email);
    Exception       
      when DUP_VAL_ON_INDEX THEN
        dbms_output.put_line('Rodne cislo neni jedinecne');
        raise DUP_VAL_ON_INDEX;
        rollback;
      when others then
        dbms_output.put_line('Nepodarilo se vlozit hosta');
        raise_application_error(-20101, 'Chyba pri vkladani hosta');
        rollback;
end;


create or replace
procedure PROJEKT_KONTROLA_OBSAZENOSTI(
p_id_pokoje in Pokoj.id_pokoje%TYPE,
p_prijezd in pobyt.prijezd%TYPE,
p_odjezd in pobyt.odjezd%TYPE
)
AS
v_id_pobytu integer;
date_colision EXCEPTION;

BEGIN
select p.id_pobytu INTO v_id_pobytu from objednavka_pokoje o, Pobyt p
where (p_prijezd < odjezd AND p_odjezd > prijezd) and p.id_pobytu = o.id_pobytu and o.id_pokoje = p_id_pokoje;
    raise date_colision;    
     exception
        when date_colision THEN
        DBMS_output.put_line(v_id_pobytu);
        when too_many_rows THEN
        DBMS_output.put_line('Pokoj je obsazen');
        when no_data_found THEN
        null;
END;


create or replace
procedure projekt_smaz_pobyt(
p_id_pobytu IN pobyt.id_pobytu%TYPE
)
AS
BEGIN
  delete from objednavka_pokoje where id_pobytu = p_id_pobytu;
  delete from objednavka_stravovani where id_pobytu = p_id_pobytu;
  delete from objednavka_sluzby where id_pobytu = p_id_pobytu;
  delete from seznam_hostu where id_pobytu = p_id_pobytu;
  
  delete from pobyt where id_pobytu = p_id_pobytu;  
  
  commit;
  exception
    when others then
      dbms_output.put_line('Chyba pri mazani pobytu');
      raise_application_error(-20101, 'Chyba pri mazani pobytu');
    rollback;
END;



create or replace
procedure projekt_spocti_cenu(
p_id_pobytu IN Pobyt.id_pobytu%TYPE
)
AS
v_pocet_dni int;
v_cena_stravovani int;
v_cena_sluzeb int;
v_celkova_suma int;
v_cena_pokoje int;
v_cena_taxy int;
BEGIN 
  select odjezd-prijezd into v_pocet_dni
  from Pobyt where id_pobytu = p_id_pobytu;
  
  select t.cena_taxy into v_cena_taxy from Taxa t, Pobyt p
  where p.id_taxy=t.id_taxy and p.id_pobytu=p_id_pobytu;
  
  select p.cena_pokoje into v_cena_pokoje from Pokoj p, objednavka_pokoje o
  where p.id_pokoje=o.id_pokoje and o.id_pobytu=p_id_pobytu;
  
  select sum(s.cena_sluzby*o.mnozstvi_sluzeb) into v_cena_sluzeb
  from sluzba s, objednavka_sluzby o
  where o.id_sluzby = s.id_sluzby and o.id_pobytu = p_id_pobytu;
  
  select sum(s.cena_stravovani*o.mnozstvi_stravovani) into v_cena_stravovani
  from stravovani s, objednavka_stravovani o
  where o.id_stravovani = s.id_stravovani and o.id_pobytu = p_id_pobytu;
  
  v_celkova_suma := (v_cena_pokoje+v_cena_taxy+v_cena_stravovani)*v_pocet_dni;
  if v_cena_sluzeb is not null then
    v_celkova_suma := v_celkova_suma + v_cena_sluzeb;
  end if;
  update Pobyt
  set celkova_suma = v_celkova_suma
  where id_pobytu = p_id_pobytu;
      
  commit;
  exception
    when others then
      dbms_output.put_line('Chyba pri pocitani celkove sumy');
      raise_application_error(-20101, 'Chyba pri pocitani celkove sumy');
    rollback;  
END;


